<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/* Start of file taxs.php */
/* Location: ./cashflow/models/taxs.php */
class Taxs extends CI_Model {
	function __construct(){
        parent::__construct(); /** Call the Model constructor */
    }
    
    function getTaxBudget($type,$date_start,$date_end){
        $SQL = " SELECT *  ";
        $SQL .=" FROM pdo_budget ";
        $SQL .=" LEFT JOIN npwp ON npwp.penerima=pdo_budget.Penerima"; 
        $SQL .=" WHERE JenisPPH='".$type."'";
        $SQL .=" AND Tanggal>='".$date_start."'";
        $SQL .=" AND Tanggal<='".$date_end."'";
        $SQL .=" ORDER BY Tanggal";
        $q = $this->db->query($SQL);
        return $Q = $q->result_array(); 
    }
    
    function getTaxData($nonpph='Non PPh'){
        $this->db->where_not_in('JenisPPH',$nonpph);
        $this->db->order_by('JenisPPH','ASC');
		$Q=$this->db->get('pajak');
		if ($Q->num_rows() > 0):
			foreach ($Q->result_array() as $row):
				$data[$row['JenisPPH']] = $row['JenisPPH'];
			endforeach;
		endif;
        $Q->free_result();
		return $data;   
	}
         
    function getExcelTaxPPH21($type,$date_start,$date_end){
        $taxs    =   $this->taxs->getTaxBudget($type,$date_start,$date_end); /** call a function getTax Table pdo budget**/
        $objPHPExcel = new PHPExcel(); /** create a new object excel **/
        /** excel properties */
        $objPHPExcel->getProperties()->setTitle("Tax PPH 21")
					->setDescription("Tax PPH 21");
					 $objPHPExcel->setActiveSheetIndex(0);
                     $objPHPExcel->getActiveSheet()->setTitle('Tax PPH 21');
        
        /** excel Page Setup */
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(Worksheet_PageSetup::ORIENTATION_PORTRAIT);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.70);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.25);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.49);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.70);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setScale(75);
        
        /** create a border **/
        $border = array('borders' => array( 'allborders' => array('style'=>Style_Border::BORDER_THIN )));
        /** create a fill coloumn **/
        $fill        = array('type'       => Style_Fill::FILL_SOLID,'rotation'=>0,'startcolor'=>array('rgb'=>'CCCCCC'),'endcolor'=>array('argb' => 'CCCCCC'));
        
        /** first row title page **/
        $row = 1;
        $col = 0;
        /** Daftar Bukti  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Daftar Bukti Potongan Pajak Pasal 21');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+7,$row);
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Company Name  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'PT. Bayu Buana Gemilang');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+7,$row);
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Periode  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Periode '.indonesian_date($date_start).' s/d '. indonesian_date($date_end));
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+7,$row);
        
         /** row & col Column Page */ 
        $row = $row + 2; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn No Col 00 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,lang('no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setWidth(5); 
        
        /** coloumn Date Col 01 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,lang('date')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+1)->setWidth(10); 
        
        /** coloumn Base Col 02 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,lang('base')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+2)->setWidth(15); 
        
        /** coloumn Fare Col 03 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('fare')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+3)->setWidth(10); 
        
        /** coloumn PPH Col 04 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,lang('pph')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+4)->setWidth(15); 
        
        /** coloumn Tax payer Col 05 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,lang('tax_payer')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+5)->setWidth(20); 
        
        /** coloumn Npwp Col 06 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,lang('npwp')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+6)->setWidth(20); 
        
        /** coloumn Npwp Col 06 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,lang('city')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+7)->setWidth(15); 
       
        /** row & col Column Content */ 
        $row = $row + 1; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        $i=1;  /** no **/
        $amount =0;
        $pph=0;
        foreach($taxs as $val):
            /** coloumn No **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$i);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                
            /** coloumn Date **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,indonesian_date($val['Tanggal']));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
                
            /** coloumn Amount **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,decimal($val['Amount'],2));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
                
            /** coloumn Presntase **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,$val['Persentase']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                
            /** coloumn PPH **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,decimal($val['PPh'],2));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                
            /** coloumn Penerima **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,$val['Penerima']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
                
            /** coloumn NPWP **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,$val['NPWP']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
                
            /** coloumn Kota **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,$val['alamat_1']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
                
            $i++; /** counter to row variabel i **/ 
            $row++; /** counter to row excel **/
            $amount=$amount+$val['Amount'];
            $pph=$pph+$val['PPh'];    
        endforeach;
        
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                
        /** coloumn Total **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,lang('total'));
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
                
        /** coloumn Amount **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,decimal($amount,2));
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
                
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,decimal($pph,2));
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                
        /** coloumn Penerima **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
                
        /** coloumn NPWP **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
                
        /** coloumn Kota **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
        
        /** Save it as an excel 2007 file */
        $objWriter = IOFactory::createWriter($objPHPExcel, "Excel2007");
        $file = "TaxPPH21.xls";
        $objWriter->save($file);
        return $file;
    }
    
    function getPdfTaxPPH21($type,$date_start,$date_end){
        $taxs    =   $this->taxs->getTaxBudget($type,$date_start,$date_end); /** call a function getTax Table pdo budget**/
        $pdf = tcpdf();
        //$pdf->setPageOrientation ('P', '', 8); 
        $pdf->setPrintHeader(false);
        $pdf->setPrintFooter(false);
        $k=0;
        $pdf->AddPage();
        /** initialization of x & y axis **/
        $x_axis_initial = 7;
        $y_axis_initial = 7;
        $x_axis = $x_axis_initial;
        $y_axis = $y_axis_initial;
        
        $pdf->SetFont('helvetica', '', 8);
        $pdf->SetFillColor(255, 255, 255);
        $pdf->SetTextColor(0, 0, 0);
        $pdf->SetDrawColor(0, 0, 0);
        $pdf->SetLineWidth(0.3); 
        
        $y_axis0=$y_axis+7; 
        $y_axis=$y_axis;

        $pdf->SetY($y_axis0);
        $pdf->SetX($x_axis_initial); 	
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial+4);
        $pdf->Cell(180,4,'Daftar Bukti Potongan Pajak '.$type, 0, 0, 'C', 1);
        
        $y_axis=$y_axis+4; 
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial+4);
        $pdf->Cell(180, 4, 'PT. Bayu Buana Gemilang', 0, 0, 'C', 1);
        
        $y_axis=$y_axis+4; 
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial+4);
        $pdf->Cell(180, 4, 'Periode '.indonesian_date($date_start).' s/d '.indonesian_date($date_end), 0, 0, 'C', 1); 
        
        $y_axis=$y_axis+3;
        $pdf->SetFillColor(200, 201, 201);
        $pdf->SetFont('helvetica', 'B', 7); 
        
       	$y_axis=$y_axis+3;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial); 	
        $pdf->Cell(7, 6, lang('no'), 1, 1, 'C', 1, '', 0, false, 'T', 'C'); 
        
        $x_axis=$x_axis+7;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(20, 6,lang('date'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+20;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(25,6,lang('base'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+25;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(30,6,lang('fare'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+30;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(20, 6,lang('pph'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+20;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 
        $pdf->Cell(30, 6,lang('tax_payer'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+30;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(28, 6,lang('npwp'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+28;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(32, 6, lang('city'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');	
        
        $y_axis=$y_axis+6;	 
        $pdf->SetFillColor(255, 255, 255);
        $pdf->SetFont('helvetica', '', 6);
        $i=1;
        $amount = 0;
        $pph    = 0;
        foreach($taxs as $row):
            $pdf->SetY($y_axis);
            $pdf->SetX($x_axis_initial); 
            $row_height=4;
            $pdf->MultiCell(7,  $row_height, $i, 1, 'C', 0, 0, '', '', true);
    		$pdf->MultiCell(20,$row_height, $row['Tanggal'], 1, 'C', 0, 0, '', '', true); 
            $pdf->MultiCell(25,$row_height, decimal($row['Amount'],2), 1, 'R', 0, 0, '', '', true); 
            $pdf->MultiCell(30,$row_height,$row['Persentase'], 1, 'R', 0, 0, '', '', true);
            $pdf->MultiCell(20,$row_height,decimal($row['PPh'],2), 1, 'R', 0, 0, '', '', true);
            $pdf->MultiCell(30,$row_height,$row['Penerima'], 1, 'L', 0, 0, '', '', true);
            $pdf->MultiCell(28,$row_height,$row['NPWP'], 1, 'L', 0, 0, '', '', true);
            $pdf->MultiCell(32,$row_height,$row['alamat_1'], 1, 'L', 0, 0, '', '', true);
    		$y_axis=$y_axis+$row_height;
            $i++;
            $amount = $amount + $row['Amount'];
            $pph    = $pph + $row['PPh'];  
        endforeach; 
        $pdf->SetFont('helvetica', 'B', 6);
    	$pdf->SetY($y_axis); $pdf->SetX($x_axis_initial); 	
    	$pdf->MultiCell(27, 4, 'Jumlah', 1, 'C', 0, 0, '', '', true);
    	$pdf->MultiCell(25, 4, number_format($amount,2,'.',','), 1, 'R', 0, 0, '', '', true);
        $pdf->MultiCell(30, 4, '', 1, 'R', 0, 0, '', '', true);
    	$pdf->MultiCell(20, 4, number_format($pph,2,'.',','), 1, 'R', 0, 0, '', '', true);
    	$pdf->MultiCell(90, 4, '', 1, 'C', 0, 0, '', '', true);
        /** return pdf file **/	
        return $pdf->Output("PPH-21.pdf","I");  
        
    }
    
    
    function getExcelTaxPPH23($type,$date_start,$date_end){
        $taxs    =   $this->taxs->getTaxBudget($type,$date_start,$date_end); /** call a function getTax Table pdo budget**/
        $objPHPExcel = new PHPExcel(); /** create a new object excel **/
        /** excel properties */
        $objPHPExcel->getProperties()->setTitle("Tax PPH 23")
					->setDescription("Tax PPH 23");
					 $objPHPExcel->setActiveSheetIndex(0);
                     $objPHPExcel->getActiveSheet()->setTitle('Tax PPH 23');
        
        /** excel Page Setup */
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(Worksheet_PageSetup::ORIENTATION_PORTRAIT);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.70);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.2);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.3);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.70);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setScale(56);
        
        /** create a border **/
        $border = array('borders' => array( 'allborders' => array('style'=>Style_Border::BORDER_THIN )));
        /** create a fill coloumn **/
        $fill        = array('type'       => Style_Fill::FILL_SOLID,'rotation'=>0,'startcolor'=>array('rgb'=>'CCCCCC'),'endcolor'=>array('argb' => 'CCCCCC'));
        
        /** first row title page **/
        $row = 1;
        $col = 0;
        /** Daftar Bukti  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Daftar Bukti Potongan Pajak Pasal 23');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+8,$row);
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Company Name  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'PT. Bayu Buana Gemilang');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+8,$row);
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Periode  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Periode '.indonesian_date($date_start).' s/d '. indonesian_date($date_end));
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+8,$row);
        
         /** row & col Column Page */ 
        $row = $row + 2; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn No Col 00 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,lang('no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setWidth(5); 
        
        /** coloumn Date Col 01 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,lang('date')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+1)->setWidth(10); 
        
        /** coloumn Service Col 02 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,lang('service')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+2)->setWidth(16); 
        
        /** coloumn Base Col 03 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('base')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+3)->setWidth(17); 
        
        /** coloumn Fare Col 04 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,lang('fare')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+4)->setWidth(10); 
        
        /** coloumn PPH Col 05 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,lang('pph')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+5)->setWidth(12); 
        
        /** coloumn Tax payer Col 06 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,lang('tax_payer')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+6)->setWidth(35); 
        
        /** coloumn Npwp Col 07 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,lang('npwp')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+7)->setWidth(19); 
        
        /** coloumn City Col 08 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,lang('address')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+8)->setWidth(50); 
       
        /** row & col Column Content */ 
        $row = $row + 1; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        $i=1;  /** no **/
        $amount =0;
        $pph=0;
        foreach($taxs as $val):
            /** coloumn No **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$i);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                
            /** coloumn Date **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,indonesian_date($val['Tanggal']));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
            
            /** coloumn Amount **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,decimal($val['Amount'],2));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
                
            /** coloumn Amount **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,decimal($val['Amount'],2));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                
            /** coloumn Presntase **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,decimal($val['Persentase'],2));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                
            /** coloumn PPH **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,decimal($val['PPh'],2));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
                
            /** coloumn Penerima **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,$val['Penerima']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_LEFT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
                
            /** coloumn NPWP **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,$val['NPWP']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
                
            /** coloumn Kota **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,$val['alamat_1']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->applyFromArray($border);
                
            $i++; /** counter to row variabel i **/ 
            $row++; /** counter to row excel **/
            $amount=$amount+$val['Amount'];
            $pph=$pph+$val['PPh'];    
        endforeach;
        
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                
        /** coloumn Total **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,lang('total'));
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
                
        /** coloumn Amount **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        
        /** coloumn Amount **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,decimal($amount,2));
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,decimal($pph,2));
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
                
        /** coloumn Penerima **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
                
        /** coloumn NPWP **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
                
        /** coloumn Kota **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,'');
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->applyFromArray($border);
        
        /** Save it as an excel 2007 file */
        $objWriter = IOFactory::createWriter($objPHPExcel, "Excel2007");
        $file = "TaxPPH21.xls";
        $objWriter->save($file);
        return $file;
    }
    
    function getPdfTaxPPH23($type,$date_start,$date_end){
        $taxs    =   $this->taxs->getTaxBudget($type,$date_start,$date_end); /** call a function getTax Table pdo budget**/
        $pdf = tcpdf();
        $pdf->setPageOrientation ('L', '', 8); 
        $pdf->setPrintHeader(false);
        $pdf->setPrintFooter(false);
        $k=0;
        $pdf->AddPage();
        /** initialization of x & y axis **/
        $x_axis_initial = 7;
        $y_axis_initial = 7;
        $x_axis = $x_axis_initial;
        $y_axis = $y_axis_initial;
        
        $pdf->SetFont('helvetica', '', 8);
        $pdf->SetFillColor(255, 255, 255);
        $pdf->SetTextColor(0, 0, 0);
        $pdf->SetDrawColor(0, 0, 0);
        $pdf->SetLineWidth(0.3); 
        
        $y_axis0=$y_axis+7; 
        $y_axis=$y_axis;

        $pdf->SetY($y_axis0);
        $pdf->SetX($x_axis_initial); 	
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial+4);
        $pdf->Cell(280,4,'Daftar Bukti Potongan Pajak '.$type, 0, 0, 'C', 1);
        
        $y_axis=$y_axis+4; 
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial+4);
        $pdf->Cell(280, 4, 'PT. Bayu Buana Gemilang', 0, 0, 'C', 1);
        
        $y_axis=$y_axis+4; 
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial+4);
        $pdf->Cell(280, 4, 'Periode '.indonesian_date($date_start).' s/d '.indonesian_date($date_end), 0, 0, 'C', 1); 
        
        $y_axis=$y_axis+3;
        $pdf->SetFillColor(200, 201, 201);
        $pdf->SetFont('helvetica', 'B', 7); 
        
       	$y_axis=$y_axis+3;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial); 	
        $pdf->Cell(7, 6, lang('no'), 1, 1, 'C', 1, '', 0, false, 'T', 'C'); 
        
        $x_axis=$x_axis+7;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(20, 6,lang('date'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+20;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(25,6,lang('service'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+25;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(28,6,lang('base'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+28;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(30,6,lang('fare'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+30;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(20, 6,lang('pph'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+20;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 
        $pdf->Cell(50, 6,lang('tax_payer'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+50;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(28, 6,lang('npwp'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+28;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(75, 6, lang('address'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');	
        
        $y_axis=$y_axis+6;	 
        $pdf->SetFillColor(255, 255, 255);
        $pdf->SetFont('helvetica', '', 6);
        $i=1;
        $amount = 0;
        $pph    = 0;
        foreach($taxs as $row):
            $pdf->SetY($y_axis);
            $pdf->SetX($x_axis_initial); 
            $row_height=4;
            $pdf->MultiCell(7,  $row_height, $i, 1, 'C', 0, 0, '', '', true);
    		$pdf->MultiCell(20,$row_height, $row['Tanggal'], 1, 'C', 0, 0, '', '', true); 
            $pdf->MultiCell(25,$row_height, decimal($row['Amount'],2), 1, 'R', 0, 0, '', '', true); 
            $pdf->MultiCell(28,$row_height, decimal($row['Amount'],2), 1, 'R', 0, 0, '', '', true); 
            $pdf->MultiCell(30,$row_height,$row['Persentase'], 1, 'R', 0, 0, '', '', true);
            $pdf->MultiCell(20,$row_height,decimal($row['PPh'],2), 1, 'R', 0, 0, '', '', true);
            $pdf->MultiCell(50,$row_height,$row['Penerima'], 1, 'L', 0, 0, '', '', true);
            $pdf->MultiCell(28,$row_height,$row['NPWP'], 1, 'L', 0, 0, '', '', true);
            $pdf->MultiCell(75,$row_height,$row['alamat_1'], 1, 'L', 0, 0, '', '', true);
    		$y_axis=$y_axis+$row_height;
            $i++;
            $amount = $amount + $row['Amount'];
            $pph    = $pph + $row['PPh'];  
        endforeach; 
        $pdf->SetFont('helvetica', 'B', 6);
    	$pdf->SetY($y_axis); $pdf->SetX($x_axis_initial); 	
    	$pdf->MultiCell(27, 4, 'Jumlah', 1, 'C', 0, 0, '', '', true);
 	    $pdf->MultiCell(25, 4, '', 1, 'R', 0, 0, '', '', true);
        $pdf->MultiCell(28, 4, number_format($amount,2,'.',','), 1, 'R', 0, 0, '', '', true);
        $pdf->MultiCell(30, 4, '', 1, 'R', 0, 0, '', '', true);
    	$pdf->MultiCell(20, 4, number_format($pph,2,'.',','), 1, 'R', 0, 0, '', '', true);
    	$pdf->MultiCell(153, 4, '', 1, 'C', 0, 0, '', '', true);
        /** return pdf file **/	
        return $pdf->Output("PPH-23.pdf","I");  
    }
    
    function getExcelTaxCuts($type,$date_start,$date_end){
        $taxs    =   $this->taxs->getTaxBudget($type,$date_start,$date_end); /** call a function getTax Table pdo budget**/
        $objPHPExcel = new PHPExcel(); /** create a new object excel **/
        /** excel properties */
        $objPHPExcel->getProperties()->setTitle("Laporan Potongan Pajak".$type)
					->setDescription("Laporan Potongan Pajak".$type);
					 $objPHPExcel->setActiveSheetIndex(0);
                     $objPHPExcel->getActiveSheet()->setTitle('Laporan Potongan Pajak'.$type);
        
        /** excel Page Setup */
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.70);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.25);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.49);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.70);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setScale(75);
        
        /** create a border **/
        $border = array('borders' => array( 'allborders' => array('style'=>Style_Border::BORDER_THIN )));
        /** create a fill coloumn **/
        $fill        = array('type'       => Style_Fill::FILL_SOLID,'rotation'=>0,'startcolor'=>array('rgb'=>'CCCCCC'),'endcolor'=>array('argb' => 'CCCCCC'));
        
        /** first row title page **/
        $row = 1;
        $col = 0;
        /** Daftar Bukti  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Laporan Potongan Pajak '.$type);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        /** merge cell from col 0 to col 9 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+9,$row);
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Company Name  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'PT. Bayu Buana Gemilang');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+9,$row);
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Periode  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Periode '.indonesian_date($date_start).' s/d '. indonesian_date($date_end));
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+9,$row);
        
         /** row & col Column Page Title */ 
        $row = $row + 2; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn No Col 00 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,lang('no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setWidth(5); 
        
        /** coloumn Date Col 01 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,lang('date')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+1)->setWidth(10); 
        
        /** coloumn Desc Col 02 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,lang('description')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+2)->setWidth(35); 
        
        /** coloumn Amount Col 03 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('amount')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+3)->setWidth(15); 
        
        /** coloumn PType Col 04 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,lang('type')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+4)->setWidth(10); 
        
        /** coloumn Dept payer Col 05 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,lang('dept')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+5)->setWidth(8); 
        
        /** coloumn Tax payer Col 06 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,lang('tax_payer')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+6)->setWidth(25); 
        
        /** coloumn Npwp Col 07 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,lang('npwp')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+7)->setWidth(19); 
        
        /** coloumn Npwp Col 08 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,lang('address')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+8)->setWidth(40);
        
        /** coloumn Npwp Col 08 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,lang('payment')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+9)->setWidth(15);
       
        /** row & col Column Content */ 
        $row = $row + 1; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        $i=1;  /** no **/
        $payment =0;
        foreach($taxs as $val):
            /** coloumn No **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$i);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                
            /** coloumn Date **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,indonesian_date($val['Tanggal']));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
                
            /** coloumn Desc **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,$val['Uraian']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
                
            /** coloumn Amount **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,decimal($val['Amount'],2));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                
            /** coloumn Type **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,$val['JenisPPH']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                
            /** coloumn DeptID **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,$val['DeptID']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
                
            /** coloumn Tax Payer  **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,$val['Penerima']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
                
            /** coloumn NPWP **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,$val['NPWP']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
            
            /** coloumn Address **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+8,$row,$val['alamat_1']);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+8,$row)->applyFromArray($border);
            
            /** coloumn Amount **/ 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,decimal($val['Payment'],2));
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->applyFromArray($border);
                
            $i++; /** counter to row variabel i **/ 
            $row++; /** counter to row excel **/
            $payment=$payment+$val['Payment'];    
        endforeach;
        
        /** coloumn Empty **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,lang('payment'));
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        /** merge cell from col 0 to col 9 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+8,$row);
        
        /** Empty **/
        for($j=1;$j<=8;$j++):
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+$j,$row,'');
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+$j,$row)->applyFromArray($border);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+$j,$row)->getFill()->applyFromArray($fill);
        endfor;    
                
        /** coloumn Total Payment **/ 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+9,$row,decimal($payment,2));
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+9,$row)->getFill()->applyFromArray($fill);
                
        /** Save it as an excel 2007 file */
        $objWriter = IOFactory::createWriter($objPHPExcel, "Excel2007");
        $file = "Potongan-Pajak-".$type.".xls";
        $objWriter->save($file);
        return $file;
    }
    
    function getPdfTaxCuts($type,$date_start,$date_end){
        $taxs    =   $this->taxs->getTaxBudget($type,$date_start,$date_end); /** call a function getTax Table pdo budget**/
        $pdf = tcpdf();
        $pdf->setPageOrientation ('L', '', 8); 
        $pdf->setPrintHeader(false);
        $pdf->setPrintFooter(false);
        $k=0;
        $pdf->AddPage();  
        /** initialization of x & y axis **/
        $x_axis_initial = 12;
        $y_axis_initial = 7;
        $x_axis = $x_axis_initial;
        $y_axis = $y_axis_initial; 
        
        $pdf->SetFont('helvetica', '', 8);
        $pdf->SetFillColor(255, 255, 255);
        $pdf->SetTextColor(0, 0, 0);
        $pdf->SetDrawColor(0, 0, 0);
        $pdf->SetLineWidth(0.3); 
        
        $y_axis0=$y_axis+7; 
        $y_axis=$y_axis;
        $pdf->SetY($y_axis0);
        $pdf->SetX($x_axis_initial); 	
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial+4);
        $pdf->Cell(275, 4, 'Laporan Potongan Pajak '.$type, 0, 0, 'C', 1);
        
        $y_axis=$y_axis+4; 
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial+4);
        $pdf->Cell(275, 4, 'PT. Bayu Buana Gemilang', 0, 0, 'C', 1); 
        
        $y_axis=$y_axis+4; 
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial+4);
        $pdf->Cell(275, 4, 'Periode '.indonesian_date($date_start).' s/d '.indonesian_date($date_end), 0, 0, 'C', 1); 
        
        $y_axis=$y_axis+3;
        $pdf->SetFillColor(205, 201, 201);
        $pdf->SetFont('helvetica', 'B', 7); 
       	$y_axis=$y_axis+3;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial); 	
        $pdf->Cell(5, 6, lang('no'), 1, 1, 'C', 1, '', 0, false, 'T', 'C'); 
        
        $x_axis=$x_axis+5;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(17, 6,lang('date'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+17;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(70, 6, lang('description'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+70;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(17, 6, lang('amount'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+17;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(13, 6,lang('type'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+13;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(14, 6,lang('dept'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        $x_axis=$x_axis+14;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(36, 6,lang('tax_payer'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
	    $x_axis=$x_axis+36;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(25, 6,lang('npwp'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
       	$x_axis=$x_axis+25;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(60, 6,lang('address'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
       	
        $x_axis=$x_axis+60;
        $pdf->SetY($y_axis);
        $pdf->SetX($x_axis); 	
        $pdf->Cell(17, 6,lang('payment'), 1, 1, 'C', 1, '', 0, false, 'T', 'C');
        
        	
        $y_axis=$y_axis+6;	 
        $pdf->SetFillColor(255, 255, 255);
        $pdf->SetFont('helvetica', '', 6);
        
        $i=1;  /** no **/
        $payment =0;
        foreach($taxs as $row):
            $desc  = strlen($row['Uraian']);
            $payer = strlen($row['Penerima']);
            
            if($desc>$payer)
                $row_height=4.5*2;	
            else
                $row_height=4;	
            
            $pdf->SetY($y_axis); $pdf->SetX($x_axis_initial); 	
    		$pdf->MultiCell(5, $row_height, $i, 1, 'C', 0, 0, '', '', true);
    		$pdf->MultiCell(17, $row_height, $row['Tanggal'].'   ', 1, 'C', 0, 0, '', '', true); 
    		$pdf->MultiCell(70, $row_height, $row['Uraian'], 1, 'L', 0, 0, '', '', true); 
    		$pdf->MultiCell(17, $row_height, number_format($row['Amount'],0,'.',','), 1, 'R', 0, 0, '', '', true); 
    		$pdf->MultiCell(13, $row_height, $row['JenisPPH'], 1, 'C', 0, 0, '', '', true); 
    		$pdf->MultiCell(14, $row_height, $row['DeptID'], 1, 'C', 0, 0, '', '', true); 
    		$pdf->MultiCell(36, $row_height, $row['Penerima'], 1, 'L', 0, 0, '', '', true); 
    		$pdf->MultiCell(25, $row_height, $row['NPWP'], 1, 'L', 0, 0, '', '', true); 
    		$pdf->MultiCell(60, $row_height, $row['alamat_1'], 1, 'L', 0, 0, '', '', true); 
    		$pdf->MultiCell(17, $row_height, number_format($row['Payment'],0,'.',','), 1, 'R', 0, 0, '', '', true); 
    		$y_axis=$y_axis+$row_height;
    		$i++;
            $payment = $payment + $row['Payment'];
        endforeach;
        
        $pdf->SetFont('helvetica', 'B', 6);
    	$pdf->SetY($y_axis);
        $pdf->SetX($x_axis_initial); 	
    	$pdf->MultiCell(257, 4, 'Total', 1, 'R', 0, 0, '', '', true);
    	$pdf->MultiCell(17, 4, number_format($payment,0,'.',','), 1, 'R', 0, 0, '', '', true); 
            
        /** return pdf file **/	
        return $pdf->Output("Laporan-Potongan.pdf","I");
    }    
}